#!/usr/bin/env python
# -*- coding: utf-8 -*-

from typing import List, Dict, Any, Optional, Tuple, Literal
from datetime import datetime

from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select
from sqlalchemy.exc import IntegrityError
from sqlalchemy import update, delete, and_, or_, between

from app.core.exceptions import DatabaseError, NotFoundError
from app.core.logging import get_logger, LoggerMixin
from app.models.database import QueryHistory, Project

# 创建日志记录器
logger = get_logger("query_history_repo")


class QueryHistoryRepository(LoggerMixin):
    """查询历史仓库
    
    管理查询历史
    """
    
    def __init__(self, session: AsyncSession):
        """初始化查询历史仓库
        
        Args:
            session: 数据库会话
        """
        self.session = session
    
    async def create(
        self,
        project_id: str,
        history_type: Literal["generation", "correction"],
        question: str,
        generated_sql: str,
        error_sql: Optional[str] = None,
        error_message: Optional[str] = None,
        db_type: str = "mysql"
    ) -> QueryHistory:
        """创建查询历史
        
        Args:
            project_id: 项目ID
            history_type: 历史类型
            question: 问题
            generated_sql: 生成的SQL
            error_sql: 错误的SQL
            error_message: 错误信息
            db_type: 数据库类型
            
        Returns:
            QueryHistory: 创建的查询历史
            
        Raises:
            DatabaseError: 当创建失败时
        """
        try:
            # 验证项目是否存在
            result = await self.session.execute(
                select(Project).filter(Project.id == project_id)
            )
            project = result.scalars().first()
            
            if not project:
                raise NotFoundError(
                    message=f"项目不存在: {project_id}",
                    resource_type="project",
                    resource_id=project_id
                )
            
            # 创建查询历史
            query_history = QueryHistory(
                project_id=project_id,
                type=history_type,
                question=question,
                generated_sql=generated_sql,
                error_sql=error_sql,
                error_message=error_message,
                db_type=db_type
            )
            
            # 添加到会话
            self.session.add(query_history)
            await self.session.flush()
            
            self.logger.info(f"已创建查询历史: {query_history.id}")
            return query_history
            
        except NotFoundError:
            raise
        except IntegrityError as e:
            await self.session.rollback()
            error_msg = f"创建查询历史失败，可能存在完整性约束问题: {str(e)}"
            self.logger.error(error_msg)
            raise DatabaseError(error_msg)
        except Exception as e:
            await self.session.rollback()
            error_msg = f"创建查询历史失败: {str(e)}"
            self.logger.error(error_msg)
            raise DatabaseError(error_msg)
    
    async def get_by_id(self, history_id: int) -> QueryHistory:
        """通过ID获取查询历史
        
        Args:
            history_id: 查询历史ID
            
        Returns:
            QueryHistory: 查询历史
            
        Raises:
            NotFoundError: 当查询历史不存在时
        """
        try:
            # 查询查询历史
            result = await self.session.execute(
                select(QueryHistory).filter(QueryHistory.id == history_id)
            )
            query_history = result.scalars().first()
            
            # 检查查询历史是否存在
            if not query_history:
                raise NotFoundError(
                    message=f"查询历史不存在: {history_id}",
                    resource_type="query_history",
                    resource_id=str(history_id)
                )
            
            return query_history
            
        except NotFoundError:
            raise
        except Exception as e:
            error_msg = f"获取查询历史失败: {str(e)}"
            self.logger.error(error_msg)
            raise DatabaseError(error_msg)
    
    async def list_by_project(
        self,
        project_id: str,
        history_type: Optional[str] = None,
        start_date: Optional[datetime] = None,
        end_date: Optional[datetime] = None,
        db_type: Optional[str] = None,
        limit: int = 10,
        offset: int = 0
    ) -> Tuple[List[QueryHistory], int]:
        """列出项目的查询历史
        
        Args:
            project_id: 项目ID
            history_type: 历史类型
            start_date: 开始日期
            end_date: 结束日期
            db_type: 数据库类型
            limit: 限制数量
            offset: 偏移量
            
        Returns:
            Tuple[List[QueryHistory], int]: 查询历史列表和总数
            
        Raises:
            DatabaseError: 当查询失败时
        """
        try:
            # 构建查询条件
            conditions = [QueryHistory.project_id == project_id]
            
            if history_type:
                conditions.append(QueryHistory.type == history_type)
            
            if db_type:
                conditions.append(QueryHistory.db_type == db_type)
            
            if start_date and end_date:
                conditions.append(between(QueryHistory.created_at, start_date, end_date))
            elif start_date:
                conditions.append(QueryHistory.created_at >= start_date)
            elif end_date:
                conditions.append(QueryHistory.created_at <= end_date)
            
            # 查询查询历史
            result = await self.session.execute(
                select(QueryHistory)
                .filter(and_(*conditions))
                .order_by(QueryHistory.created_at.desc())
                .limit(limit)
                .offset(offset)
            )
            query_history_list = result.scalars().all()
            
            # 查询总数
            count_result = await self.session.execute(
                select(QueryHistory.id)
                .filter(and_(*conditions))
            )
            total = len(count_result.scalars().all())
            
            return list(query_history_list), total
            
        except Exception as e:
            error_msg = f"列出查询历史失败: {str(e)}"
            self.logger.error(error_msg)
            raise DatabaseError(error_msg)
    
    async def search_by_question(
        self,
        project_id: str,
        query: str,
        history_type: Optional[str] = None,
        limit: int = 5
    ) -> List[QueryHistory]:
        """通过问题搜索查询历史
        
        Args:
            project_id: 项目ID
            query: 搜索查询
            history_type: 历史类型
            limit: 限制数量
            
        Returns:
            List[QueryHistory]: 查询历史列表
            
        Raises:
            DatabaseError: 当搜索失败时
        """
        try:
            # 构建查询条件
            conditions = [QueryHistory.project_id == project_id]
            
            # 添加历史类型条件
            if history_type:
                conditions.append(QueryHistory.type == history_type)
            
            # 添加问题搜索条件
            conditions.append(QueryHistory.question.like(f"%{query}%"))
            
            # 执行查询
            result = await self.session.execute(
                select(QueryHistory)
                .filter(and_(*conditions))
                .order_by(QueryHistory.created_at.desc())
                .limit(limit)
            )
            query_history_list = result.scalars().all()
            
            return list(query_history_list)
            
        except Exception as e:
            error_msg = f"搜索查询历史失败: {str(e)}"
            self.logger.error(error_msg)
            raise DatabaseError(error_msg)